﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL.DataOperate
{
    public class UserDAL:DAL.App_Code.DBBase
    {
        public UserDAL() : 
            base() { }
        #region 查找
        public DataSet SelectAll()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from vi_User");

            SqlParameter[] pa = { };

            return ExecuteString(sqlStr.ToString(), pa, "tbUser_Infor");
        }
        public DataSet SelectByName(String UserName)
        {
            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append("select * from vi_User ");
            sqlStr.Append("where User_Namestr = ");
            sqlStr.Append("@User_Namestr");

            SqlParameter[] pa = { 
               new SqlParameter ("@User_Namestr",UserName)
                               };

            return ExecuteString(sqlStr.ToString(), pa, "tbUser_Infor");
        }
        public DataSet SelectByID(int UserId)
        {
            StringBuilder sqlStr = new StringBuilder();
            DataSet ds = new DataSet();

            sqlStr.Append("select * from vi_User ");
            sqlStr.Append("where User_Idint = ");
            sqlStr.Append("@User_Idint");

            SqlParameter[] pa = { 
               new SqlParameter ("@User_Idint",UserId)
                               };

            return ExecuteString(sqlStr.ToString(), pa, "tbUser_Infor");
        }

        public bool Insert(Model.User user)
        {
            int affectedRow = 0;
            int returnValue = 0;

            SqlParameter[] pa = {
                new SqlParameter("@User_Addressstr",user.User_Addressstr),
                new SqlParameter("@User_Answerstr",user.User_Answerstr),
                new SqlParameter("@User_Mailstr",user.User_Mailstr),
                new SqlParameter("@User_Namestr",user.User_Namestr),
                new SqlParameter("@User_Passwordstr",user.User_Passwordstr),
                new SqlParameter("@User_Questionstr",user.User_Questionstr),
                new SqlParameter("@User_Telephonestr",user.User_Telephonestr)
                                };

            returnValue = ExecuteRetProcedure("sp_InsertUser", pa, out affectedRow);
            return returnValue > 0 ? true : false;
        }
        public bool Update(Model.User user)
        {
            int affectedRow = 0;
            int returnValue = 0;

            SqlParameter[] pa = {
                new SqlParameter("@User_Addressstr",user.User_Addressstr),
                new SqlParameter("@User_Answerstr",user.User_Answerstr),
                new SqlParameter("@User_Mailstr",user.User_Mailstr),
                new SqlParameter("@User_Namestr",user.User_Namestr),
                new SqlParameter("@User_Questionstr",user.User_Questionstr),
                new SqlParameter("@User_Telephonestr",user.User_Telephonestr)
                                };

            returnValue = ExecuteRetProcedure("sp_UpdateUser", pa, out affectedRow);
            return returnValue > 0 ? true : false;
        }
        public bool UpdatePassword(String UserName, String oldPass, String newPass)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            DataSet ds = new DataSet();
            ds = SelectByName(UserName);                                             // 获得原密码
            if ((ds == null) ||
               (ds.Tables.Count == 0) ||
               (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            {
                return false;
            }
            else
            {
                if (ds.Tables[0].Rows[0]["User_Passwordstr"].ToString() == oldPass)  // 原密码输入是否正确
                {
                    sqlStr.Append("update vi_User ");
                    sqlStr.Append("set User_Passwordstr = @User_Passwordstr ");
                    sqlStr.Append("where ");
                    sqlStr.Append("User_Namestr = @User_Namestr ");

                    SqlParameter[] pa ={
                           new SqlParameter("@User_Passwordstr",newPass),
                           new SqlParameter("@User_Namestr",UserName)
                               };

                    ExecuteString(sqlStr.ToString(), pa, out affectedRow);
                    return affectedRow > 0 ? true : false;
                }
                else
                {
                    return false;
                }
            }
        }
        public bool Delete(int UserId)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;

            sqlStr.Append("delete from vi_User ");
            sqlStr.Append("where User_Idint = ");
            sqlStr.Append("@User_Idint");

            SqlParameter[] pa = { 
               new SqlParameter ("@User_Idint",UserId)
                               };

            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;
        }

        public int GetUserId(String UserName)
        {
            DataSet ds = new DataSet();
            ds = SelectByName(UserName);
            if ((ds == null) ||
             (ds.Tables.Count == 0) ||
             (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            {
                return -1;
            }
            else
            {
                return Int32.Parse(ds.Tables[0].Rows[0]["User_Idint"].ToString());
            }
        }
        #endregion
    }
}
